package cn.itcast.jsjwl.dao;

import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import cn.itcast.jsjwl.domain.Student;
import cn.itcast.jsjwl.domain.Teacher;
import cn.itcast.jsjwl.utils.JDBCUtils;

public class StuDao {

	public Student validateUserExist(String um) throws SQLException {
		String sql="SELECT *  FROM stu_info WHERE stuid=?";
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		return qr.query(sql, new BeanHandler<Student>(Student.class),um);
	}
	public Student stuLogin(String um, String up) throws SQLException {
		String sql="SELECT *  FROM stu_info WHERE stuid=? AND loginpwd=?";
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		return qr.query(sql, new BeanHandler<Student>(Student.class),um,up);
	}
	public Student getStudentByName(String userName) throws SQLException {
		String sql="SELECT * FROM stu_info WHERE stuid=?";
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		return qr.query(sql, new BeanHandler<Student>(Student.class),userName);
	}
	public static int findTotalRecords() throws SQLException {
		String sql="SELECT COUNT(*) FROM stu_info";
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		Long num=(Long)qr.query(sql, new ScalarHandler());
		return num.intValue();
	}
	public List<Student> findStudentsWithPage(int startIndex, int pageSize) throws SQLException {
		String sql="SELECT *FROM stu_info LIMIT ?,?";
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		return qr.query(sql, new BeanListHandler<Student>(Student.class),startIndex,pageSize);
	}
	public void addStudent(Student student) throws SQLException {
		String sql="INSERT INTO stu_info (stuid,name,class_id,sex,tel,faculty,loginpwd,testscore,expscore,videoscore,sumscore) VALUES( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? );";
		Object[] params= {student.getStuid(),student.getName(),student.getClass_id(),student.getSex(),student.getTel(),student.getFaculty(),student.getLoginpwd(),student.getTestscore(),student.getExpscore(),student.getVideoscore(),student.getSumscore() };
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		qr.update(sql,params);	
	}
	public void delStudentById(String sId) throws SQLException {
		String sql="delete FROM stu_info WHERE stuid=?";
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		qr.update(sql,sId);
		
	}
	public void updateStudent(Student stu) throws SQLException {
		String sql="UPDATE stu_info SET name= ? ,sex= ? ,tel= ?  ,faculty= ? ,loginpwd= ? ,class_id= ?,online= ? ,activity=? WHERE stuid= ? ";
		Object[] params= {stu.getName(),stu.getSex(),stu.getTel(),stu.getFaculty(),stu.getLoginpwd(),stu.getClass_id() , stu.getOnline() ,stu.getActivity(),stu.getStuid()};
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		qr.update(sql,params);	
	}
	public List<Student> findStudentByClass(int startIndex, int pageSize, Teacher teacher) throws SQLException {
		String sql="SELECT *FROM stu_info WHERE class_id= ? LIMIT ?,?";

		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		return qr.query(sql, new BeanListHandler<Student>(Student.class),teacher.getClass_id(),startIndex,pageSize);
	}
	public List<Student> findOnlineStudentByClass(int startIndex, int pageSize, Teacher teacher) throws SQLException {
		  String sql="SELECT *FROM stu_info WHERE class_id= ? AND online=1 LIMIT ?,?";
		  QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		  return qr.query(sql, new BeanListHandler<Student>(Student.class),teacher.getClass_id(),startIndex,pageSize);
		 
	}
	public int getOnlineNum(String classId) throws SQLException {
		String sql=" SELECT COUNT(*) FROM stu_info WHERE online = 1 AND class_id = " + classId;
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		Long num=(Long)qr.query(sql, new ScalarHandler());
		return num.intValue();
	}
	public int ifOnline(String um) throws SQLException {
		String sql=" SELECT COUNT(*) FROM stu_info WHERE online = 1 AND stuid = " + um;
		QueryRunner qr=new QueryRunner(JDBCUtils.getDataSource());
		Long n=(Long)qr.query(sql, new ScalarHandler());
		return n.intValue();
	}
	

}
																											